#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
Helper package to fetch latest meteo observations from http://www.meteo.pt/
Uses libraries:
- sys,os,time,random,getopt,locale,codecs,datetime          
- urllib,urllib2    part of Python 2.6
- sqlite3           
- BeautifulSoup     http://www.crummy.com/software/BeautifulSoup
- pymetar           http://freecode.com/projects/pymetar

Please consult the sources of the previous packages for the respective licensing 
information. This Software is licensed as described below. This does *NOT* include 
the data which it gathers from the site of the Instituto de Meteorologia, which is 
described at http://www.meteo.pt/export/sites/default/bin/docs/institucionais/cd_114_09.pdf
(note: text in Portuguese).

Software Author: Joao Batista
Software URL: http://code.google.com/p/pymeteopt/
Software License: The MIT License

    Copyright (c) 2012 Joao Batista
    
    Permission is hereby granted, free of charge, to any person obtaining a copy of
    this software and associated documentation files (the "Software"), to deal in
    the Software without restriction, including without limitation the rights to
    use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies
    of the Software, and to permit persons to whom the Software is furnished to do
    so, subject to the following conditions:
    
    The above copyright notice and this permission notice shall be included in all
    copies or substantial portions of the Software.
    
    THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
    IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
    FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
    AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
    LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
    OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
    SOFTWARE.

"""

import sys
import os
import time
import random
import getopt
import locale
import codecs
import datetime
import urllib
import urllib2
import BeautifulSoup
import sqlite3
#import pymetar

PYMETEOPT_DB_BASE=os.path.realpath(__file__)[:-len(os.path.basename(__file__))]
PYMETEOPT_DB='%(basedir)s%(filename)s.db' % {'basedir':PYMETEOPT_DB_BASE,'filename':os.path.basename(__file__).replace('.pyc','.py')}

def stationMap():
    return {
        u'Aveiro (Universidade)':702,
        u'Beja':562,
        u'Bragança':575,
        u'Cabo Carvoeiro':531,
        u'Castelo Branco':570,
        u'Coimbra (Aeródromo)':548,
        u'Évora (Aeródromo)':558,
        u'Faro (Aeroporto)':554,
        u'Flores (Aeroporto)':501,
        u'Funchal':522,
        u'Horta (Obs. Príncipe Alberto Mónaco)':506,
        u'Leiria (Aeródromo)':718,
        u'Lisboa (G.Coutinho)':579,
        u'Ponta Delgada (Aeroporto)':512,
        u'Penhas Douradas':568,
        u'Portalegre':571,
        u'Porto (Aeroporto)':545,
        u'Porto Santo (Aeroporto)':524,
        u'Sagres':533,
        u'Santarém, Fonte Boa':734,
        u'Sines':541,
        u'Viana Castelo, Chafé':551,
        u'Vila Real':567,
        u'Viseu (Aeródromo)':560,
    }

def html():
    urlbase = 'http://www.meteo.pt/en/html.jsp'
    request = urllib2.Request(urlbase)
    response = urllib2.urlopen(request)
    print 'Reading from %s ...' % urlbase
    page = response.read()
    response.close()
    return BeautifulSoup.BeautifulSoup(page.replace('"text/html; charset=iso-8859-1"','"text/html; charset=ISO-Latin-1"'))

def getObsTimestampUTC(soup):
    e0 = soup.findAll('table',attrs={'class':'tablelist_wid','summary':'links'})
    e1 = BeautifulSoup.BeautifulSoup(str(e0))
    dt = e1.findAll('td')[1].contents[0].split(' ')[2:4]
    obststamp = datetime.datetime(int(dt[0].split('-')[0]), int(dt[0].split('-')[1]), int(dt[0].split('-')[2]), int(dt[1].split(':')[0]), int(dt[1].split(':')[1]))
    return obststamp

def getObsList(soup):
    result = []
    element = soup.findAll('table',attrs={'class':'tablelist_wid','summary':'variaveis'})[0]
    obslist = BeautifulSoup.BeautifulSoup(str(element)).findAll('tr')[2:]
    obststamp = getObsTimestampUTC(soup)
    for obs in obslist:
        tdList = obs.findAll('td')
        v = {}
        localname = tdList[0].contents[0]    #str
        local = localname.split('(')[0].split(',')[0].strip()
        v['tstamp'] = obststamp
        v['stationId'] = '08%03i' % stationMap()["%s"%localname]
        v['local'] = localname
        v['weather'] = tdList[1].contents[0]  #str
        v['temp_C'] = str(tdList[2].contents[0])   #float
        v['wind_kph'] = str(tdList[3].contents[0]) #float
        v['wind_dir'] = str(tdList[4].contents[0]) #str
        v['rh_perc'] = str(tdList[5].contents[0])  #float
        v['prec_mm'] = str(tdList[6].contents[0])  #float
        v['p_hPa'] = str(tdList[7].contents[0])    #float
        v['cc_perc'] = str(tdList[8].contents[0])  #float
        v['snow_cm'] = str(tdList[9].contents[0])  #float
        result.append(v)
    return result

def storeDB(obslist):
    conn = sqlite3.connect(PYMETEOPT_DB)
    numInserts=0
    with conn:
        cur = conn.cursor()
        for obs in obslist:
            tblname = 'STATION%s' % obs['stationId']
            cur.execute('''CREATE TABLE IF NOT EXISTS %s
                (tstamp_utc TIMESTAMP PRIMARY KEY, Tar_C DOUBLE, pres_hPa DOUBLE, prec_mm DOUBLE, HRel DOUBLE, 
                 Wind_kph DOUBLE, Wind_dir TEXT, CloudCover DOUBLE, snow_cm DOUBLE, weather TEXT)''' % tblname)
            try:
                cur.execute('''INSERT INTO %s VALUES (?,?,?,?,?,?,?,?,?,?)''' % tblname,
                    (obs['tstamp'],obs['temp_C'],obs['p_hPa'],obs['prec_mm'],obs['rh_perc'],obs['wind_kph'],obs['wind_dir'],
                     obs['cc_perc'], obs['snow_cm'], obs['weather']))
                print '%5s %s' % (obs['stationId'], obs['local'])
                numInserts = numInserts+1
            except sqlite3.IntegrityError as error:
                cur.execute('SELECT COUNT(*) FROM %s WHERE tstamp_utc=?'%tblname, (obs['tstamp'],) )
                numRows = cur.fetchone()[0]
                if numRows > 0:
                    #raise Exception('Observation %(tstamp)s for station %(station)s already in database; skipping.' % {'tstamp':obs['tstamp'],'station':''})
                    pass
                else:
                    print error
    conn.close()
    return numInserts

def getLatest(quiet=True):
    """Call this function to put current observations into database."""
    start = datetime.datetime.utcnow()
    print start
    soup = html()
    obststamp = getObsTimestampUTC(soup)
    print str(obststamp)[:-3]
    obslist = getObsList(soup)
    numInserts = storeDB(obslist)
    stop = datetime.datetime.utcnow()
    took = stop-start
    if numInserts==0:
        print 'Execution took %i.%i seconds.' % (took.seconds,took.microseconds/10000)
    else:
        print 'Stored data for %i stations in %i.%i seconds.' % (numInserts,took.seconds,took.microseconds/10000)
    return numInserts

def showall(stationId,since=None,until=None,showHeader=False):
    if stationId==None or type(stationId)!=int or stationId<500 or stationId>999:
        raise Exception('Error in stationId')
    if since!=None and type(since)!=datetime.datetime:
        raise Exception('Incorrect type of since')
    if until!=None and type(until)!=datetime.datetime:
        raise Exception('Incorrect type of until')
    conn = sqlite3.connect(PYMETEOPT_DB)
    with conn:
        cur = conn.cursor()
        rows = None
        if since==None and until==None:
            rows=cur.execute('SELECT * FROM STATION08%03i'%stationId)
        elif until==None:
            rows=cur.execute('SELECT * FROM STATION08%03i WHERE tstamp_utc>=?'%stationId, (since,))
        elif since==None:
            rows=cur.execute('SELECT * FROM STATION08%03i WHERE tstamp_utc<?'%stationId, (until,))
        else:
            rows=cur.execute('SELECT * FROM STATION08%03i WHERE tstamp_utc>=? AND tstamp_utc<?'%stationId, (since,until,))
        if showHeader:
# TODO improve headers
            print "Id   |TimeStamp          |T.ºC|P_hPa |Prc|HR %|Wind|  |CC %|Snow|Weather"
        for row in rows:
#            print "08%s|%s|%s|%s|%s|%s|%s|%s" % (stationId, row[0],row[1],row[2],row[3],row[4],row[5],row[6])
            print "08%s|%s" % (stationId,'|'.join(map(unicode,row)))
    conn.close()

def stationList():
    '''Returns list of stations as list of items: (station_block,station_id,city,place,station_type,latitude,longitude,altitude)'''
    return [
        (8,503,u'Corvo',u'',                                    u'CLIMAT',39.700,-31.100,   0),
        (8,530,u'Cabo Carvoeiro',u'',                           u'CLIMAT',39.400, -9.400,  32),
        (8,537,u'Sintra',u'Granja',                             u'CLIMAT',38.800, -9.300, 133),
        (8,538,u'Sagres',u'',                                   u'CLIMAT',39.000, -9.000,   0),
        (8,549,u'Coimbra',u'I.Geofisico',                       u'CLIMAT',40.200, -8.400, 141),
        (8,702,u'Aveiro',u'',                                   u''      ,40.635, -8.660,   3),
        (8,531,u'Cabo Carvoeiro',u'',                           u''      ,39.400, -9.400,  32),
        (8,548,u'Coimbra',u'Aerodromo',                         u''      ,40.150, -8.467, 179),
        (8,558,u'Evora',u'Aerodromo',                           u''      ,38.600, -7.900, 309),
        (8,506,u'Horta',u'Obs. Pincipe Alberto',                u''      ,38.500,-28.600,  62),
        (8,718,u'Leiria',u'Aerodromo',                          u''      ,0,0,0),
        (8,579,u'Lisboa',u'Gago Coutinho',                      u''      ,38.700, -9.100,  95),
        (8,568,u'Penhas Douradas',u'',                          u''      ,40.420, -7.550,1380),
        (8,545,u'Porto',u'Pedras Rubras',                       u''      ,41.200, -8.700,  70),
        (8,524,u'Porto Santo',u'',                              u''      ,33.100,-16.300,  82),
        (8,533,u'Sagres',u'',                                   u''      ,37.000, -8.950,  25),
        (8,734,u'Santarem',u'Fonte Boa',                        u''      ,0,0,0),
        (8,541,u'Sines',u'Montes Chaos',                        u''      ,37.950, -8.867,  98),
        (8,551,u'Viana do Castelo',u'Chafe',                    u''      ,41.700, -8.800,  16),
        (8,567,u'Vila Real',u'',                                u''      ,41.320, -7.730, 481),
        (8,560,u'Viseu',u'Aerodromo',                           u''      ,40.717, -7.883, 644),
        (8,511,u'Angra do Heroismo',u'Observatorio / Terceira', u'SINOP' ,38.650,-27.217,  74),
        (8,102,u'Aveiro',u'Universidade',                       u'CLIMAT',40.633, -8.650,   5),
        (8,562,u'Beja',u'',                                     u'SINOP' ,38.017, -7.867, 246),
        (8, 23,u'Braga',u'Posto Agrario',                       u'CLIMAT',41.550, -8.400, 190),
        (8,575,u'Braganca',u'',                                 u'SINOP' ,41.800, -6.733, 690),
        (8,570,u'Castelo Branco',u'C.C.',                       u'SINOP' ,39.833, -7.467, 386),
        (8,107,u'Coimbra',u'Becanta',                           u'CLIMAT',42.200, -8.540,  35),
        (8,557,u'Evora',u'Cidade',                              u'CLIMAT',38.567, -7.900, 309),
        (8,554,u'Faro',u'Aeroporto',                            u'SINOP' ,37.017, -7.967,   8),
        (8,501,u'Flores',u'Aeroporto',                          u'SINOP' ,39.450,-31.117,  28),
        (8,522,u'Funchal',u'Observatorio / Madeira',            u'SINOP' ,32.633,-16.883,  58),
        (8, 82,u'Guarda',u'',                                   u'CLIMAT',40.533, -7.267,1019),
        (8,535,u'Lisboa',u'I. Geofisico',                       u'SINOP' ,38.717, -9.133,  77),
        (8,512,u'Ponta Delgada',u'Nordela / S. Miguel',         u'SINOP' ,37.733,-25.683,  71),
        (8,571,u'Portalegre',u'',                               u'SINOP' ,39.283, -7.417, 597),
        (8,546,u'Porto',u'Serra do Pilar',                      u'CLIMAT',41.133, -8.600,  93),
        (8,132,u'Santarem',u'Escola Agraria',                   u'CLIMAT',39.250, -8.900,  54),
        (8,170,u'Setubal',u'Estacao de Fruticultura',           u'CLIMAT',38.533, -8.883,  35),
        (8,543,u'Viana do Castelo',u'Meadela',                  u'SINOP' ,41.700, -8.800,  16),
        (8,566,u'Vila Real',u'',                                u'SINOP' ,41.317, -7.733, 481),
        (8, 75,u'Viseu',u'',                                    u'SINOP' ,40.667, -7.900, 443),
    ]

def createStationTable():
    conn = sqlite3.connect(PYMETEOPT_DB)
    with conn:
        cur = conn.cursor()
        cur.execute('CREATE TABLE IF NOT EXISTS STATIONLIST(block INTEGER, id INTEGER PRIMARY KEY, name TEXT, local TEXT, type TEXT, latitude DOUBLE, longitude DOUBLE, altitude INTEGER)')
        for values in stationList():
            print values
            cur.execute('INSERT INTO STATIONLIST VALUES (?,?,?,?,?,?,?,?)', values)
    conn.close()

def wait(interval_seconds=10,message='%i'):
    if os.isatty(sys.stdin.fileno()):
        for i in range(interval_seconds,-1,-1): 
            sys.stdout.write('\r'+message % i)
            sys.stdout.flush()
            time.sleep(1)
    else:
        print message%interval_seconds
        time.sleep(interval_seconds)

def main(argv=sys.argv):
    if os.name == 'nt':
        os.system('dir /a %s'%PYMETEOPT_DB)
    else:
        os.system('ls -lh %s'%PYMETEOPT_DB)
    maxwait = 120
    wait_seconds = int(maxwait*random.random())
    wait(wait_seconds,'Waiting %i seconds to start... ')
    print '\rStarting acquisition.                    '
    counter = getLatest();
    print 'Done.'
    wait(5,'Exiting in %i...')
    print '\r                '
    sys.exit(counter)

if __name__ == "__main__":
    # Fix codec screw-up using recipe from http://7bits.nl/blog/2010/06/12/silly-python-unicode-mistake
    sys.stdout = codecs.getwriter(locale.getpreferredencoding())(sys.stdout, errors='replace')
    main()

###################################################################################################

class QuantidadeException:
    def __init__(self,value):
        self.value=value
    def __str__(self):
        return repr(self.value)

class EstacaoException:
    def __init__(self,value):
        self.value=value
    def __str__(self):
        return repr(self.value)

class Quantidade:
    """This class contains a unit of information, composed of name, value and unit (if necessary)."""
    def __init__(self,nome,value,unit=''):
        if nome is None:
            raise QuantidadeException('nome cannot be None')
        self.nome=nome
        if value is None:
            raise QuantidadeException('value of',nome,'cannot be None')
        self.value=value
        self.unit=unit
    def __str__(self):
         return "%s" % self.value
#         return str(self.value)
#        return '%(nome)s = %(value)s %(unit)s' % {'nome':self.nome,'value':self.value,'unit':self.unit}
    def __int__(self):
        return int(self.value)
    def __float__(self):
        return float(self.value)

class Estacao:
    """
    This class knows how to get current ground station observation data from www.meteo.pt.
    It can also store() the latest fetched data to an SQLite3 database, or recall() from the database by timestamp.
    License: Public domain.
    """
    _url = 'http://pda.meteo.pt/observacao.jsp'
    def _hasStationId(self):
        if self.StationId is '' or None:
            raise EstacaoException('StationId is empty')
        else:
            return True
    def __str__(self):
        string = None
        if self._IsDefined:
# TODO fix broken handling of encoding
#            stationName = None
#            try:
#                stationName = self.Data['local']
#            except KeyError:
#                stationName = ''
            string = '08%(stationId)s|%(tstamp)s|%(temp).1f|%(pres).1f|%(prec).1f|%(rh).1f|%(wind_vel).1f|%(wind_dir)s' % {'stationId':self.StationId, 'tstamp':self.Timestamp, 'prec':self.Data['prec'], 'pres':self.Data['p'], 'temp':self.Data['t_ar'], 'rh':self.Data['rh'], 'wind_vel':self.Data['wind_kph'], 'wind_dir':self.Data['wind_dir']}
        else:
            pass
        return "%s"%string
    def __init__(self,StationId=''):
        self._IsDefined=False
        self._IsStored=False
        self._IsLocated=False
        self.StationId=StationId
        self.Timestamp=None
        self._hasStationId()
        self.StationPrefix = '%02i'%8
        self.htmldoc=None
        self.Data={}
        self.cidade=''
        self.local=''
        self.tipo=''
        self.latitude=0.0
        self.longitude=0.0
        self.altitude=0
        self._getLatLonAlt()
    def _getLatLonAlt(self):
        if not self._IsLocated:
            for station in stationList():
                if (not self._IsLocated) and (station[1]==self.StationId):
                    self.cidade=station[2]
                    self.local=station[3]
                    self.tipo=station[4]
                    self.latitude=station[5]
                    self.longitude=station[6]
                    self.altitude=station[7]
                    self._IsLocated=True
                    break
        else:
            pass
    def kml(self):
        """TODO FIX THIS (unicode/ascii encoding). Get information as KML (Google Earth) string."""
        encoding = 'utf8'
        result = None
        kmlfmt = '''<?xml version="1.0" encoding="utf-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://www.google.com/kml/ext/2.2">
    <Style id="SurfaceObservation">
        <IconStyle id="styleIcon">
            <Icon>
                <scale>1.0</scale>
                <href></href>
            </Icon>
        </IconStyle>
    </Style>
    <Placemark id="%(StationBlock)02i%(StationId)03i">
        <name><![CDATA[%(local)s]]></name>
        <description>
            <p>Estação: %(StationBlock)02i%(StationId)03i</p>
            <p>Hora: %(tstamp)s</p>
            <p>Local: %(local)s</p>
            <p>Temperatura: %(temp).1f %(tempunits)s</p>
            <p>Pressão: %(pres).1f %(presunits)s</p>
            <p>Source: <a href="%(baseUrl)s">%(baseUrl)s</a></p>
        </description>
        <!--<styleUrl>#SurfaceObservation</styleUrl>-->
        <Point>
            <coordinates>%(longitude)f,%(latitude)f,%(altitude)i</coordinates>
        </Point>
    </Placemark>
</kml>'''
        result = kmlfmt % { 'baseUrl':'http://pda.meteo.pt/',
            'StationBlock':int(self.StationPrefix), 'StationId':int(self.StationId),
            'tstamp':str(self.Timestamp), 'local':self.Data['local'].value,
            'latitude':float(self.latitude), 'longitude':float(self.longitude), 'altitude':float(self.altitude),
            'temp':float(self.Data['t_ar'].value), 'tempunits':self.Data['t_ar'].unit,
            'pres':float(self.Data['p'].value), 'presunits':self.Data['p'].unit,
        }
        return result
    def xml(self):
        """Get observation information as XML format string."""
        result = None
        if self._IsDefined:
            encoding='utf8'
            fmtstr = '''<MeteoObservation baseUrl="%(baseUrl)s">
    <Station countryCode="%(countryCode)s" block="%(StationBlock)02i" id="%(StationId)03i">
        <Location latitude="%(latitude).5f" longitude="%(longitude).5f" altitude_meters="%(altitude).0f" country="%(country)s"><![CDATA[%(local)s]]></Location>
    </Station>
    <Timestamp format="%(tstampformat)s">%(timestamp)s</Timestamp>
    <SurfaceObservationData>
        <Temperature units="%(tempunits)s">%(temp).1f</Temperature>
        <Pressure units="%(presunits)s">%(pres).1f</Pressure>
        <Precipitation units="%(precunits)s">%(prec).1f</Precipitation>
        <RelativeHumidity units="%(rhunits)s">%(rh).0f</RelativeHumidity>
        <Wind>
            <Speed units="%(windspeedunits)s">%(windspeed).1f</Speed>
            <BlowingFrom units="$(winddirunits)s">%(winddir)s</BlowingFrom>
        </Wind>
    </SurfaceObservationData>
</MeteoObservation>''' 
#       These are not available in http://pda.meteo.pt/
#        <CloudCover units="%(cloudcoverunits)s">%(cloudcover)s</CloudCover>
#        <SnowDepth units="%(snowunits)s">%(snow)f</SnowDepth>
#        <Weather language="%(weatherLang)s"><![CDATA[%(weather)s]]></Weather>
            result = fmtstr % { 'baseUrl':'http://pda.meteo.pt', 'countryCode':'pt',
              'StationBlock':int(self.StationPrefix), 'StationId':int(self.StationId),
              'latitude':float(self.latitude), 'longitude':float(self.longitude), 'altitude':float(self.altitude),
              'country':'pt', 'local':self.Data['local'].value, 
              'timestamp':str(self.Timestamp), 'tstampformat':'yyyy-mm-dd hh:mm:ss',
              'temp':float(self.Data['t_ar'].value), 'tempunits':self.Data['t_ar'].unit.decode(encoding),
              'pres':float(self.Data['p'].value), 'presunits':self.Data['p'].unit.decode(encoding),
              'prec':float(self.Data['prec'].value), 'precunits':self.Data['prec'].unit.decode(encoding),
              'rh':float(self.Data['rh'].value), 'rhunits':self.Data['rh'].unit.decode(encoding),
              'windspeed':float(self.Data['wind_kph'].value), 'windspeedunits':self.Data['wind_kph'].unit.decode(encoding),
              'winddir':str(self.Data['wind_dir'].value).decode(encoding), 'winddirunits':'',
##              'cloudcover':self.Data['cc_perc'].value, 'cloudcoverunits':self.Data['cc_perc'].unit.decode(encoding),
##              'snow':self.Data['snow_cm'].value, 'snowunits':self.Data['snow_cm'].unit.decode(encoding),
##              'weatherLang':'pt', 'weather':self.Data['weather'].value,
#              'cloudcover':'', 'cloudcoverunits':'', 'snow':0.0, 'snowunits':'', 'weatherLang':'pt','weather':'',
            }
        else:
           result = '<MeteoObservation baseUrl="%(baseUrl)s"></MeteoObservation>' % {'baseUrl':baseUrl,}
        return result
    def _readUrl(self):
        if self._hasStationId():
            urlpars = urllib.urlencode( {'selLocal':self.StationId} )
            request = urllib2.Request(self._url,urlpars)
            response = urllib2.urlopen(request)
            page = response.read()
#            print 'HTML read for StationId=%s' % self.StationId
            self.url="%s?selLocal=%i"%(self._url,self.StationId)
            return page
        else:
            return None
    def _empty(self):
        td = BeautifulSoup.Tag(BeautifulSoup.BeautifulSoup(),'td')
        p =  BeautifulSoup.Tag(BeautifulSoup.BeautifulSoup(),'p')
        em = BeautifulSoup.Tag(BeautifulSoup.BeautifulSoup(),'em')
        p.insert(0, em)
        td.insert(0, p)
        return td
    def _obstimestamp(self,hour):
        now = datetime.datetime.utcnow()
        tstamp = None
        delta = datetime.timedelta(0)
        if now.hour < hour:
            delta = datetime.timedelta(1)
        tstamp = datetime.datetime(now.year,now.month,now.day, hour) - delta
        return tstamp
    def _encodethis(self,thing,encoding='utf-8'):
        return str(thing.encode(encoding))
    def fetch(self):
        """Get (and parse) data from http://pda.meteo.pt/ for the instanced Station ID. This can then be store()'d on the database."""
        if self._IsDefined:
            raise Exception('This instance already has data for station %s!' % self.StationId)
        self.htmldoc = self._readUrl()
        if self.htmldoc is None:
            raise EstacaoException('Could not read station ',self.StationId,'.')
        else:
            soup = BeautifulSoup.BeautifulSoup(self.htmldoc)
            HoraObs = int( soup.findAll('p',attrs={'class':'textoheader2'})[0].contents[0][26:-1] )
            self.Timestamp = self._obstimestamp(int(HoraObs))
#            print 'Observation timestamp = %s UTC' % self.Timestamp
            thevars={'local':1, 't_ar':2, 'rh':3, 'prec':6, 'wind':4, 'p':7}
            encoding='utf-8'
            for var in thevars:
                quant = soup.findAll('td',attrs={'class':'center-obscontain%i'%thevars[var]})
                if thevars[var]==1:
                    quant.insert(-1, self._empty())
                if thevars[var]==4:
                    quant.extend( soup.findAll('td',attrs={'class':'center-obscontain%i'%41}) )
                    quant.extend( soup.findAll('td',attrs={'class':'center-obscontain%i'%42}) )
                name = "%s"%quant[0].p.contents[0]
                if len(quant)>=3:
                    units = ("%s"%quant[1].p.em).replace('<em>','').replace('</em>','')
                    value = "%s"%quant[2].p.contents[0]
                    if len(units) > 0 and value=='---':
                        value='0'
                    if thevars[var]==4 and len(quant)>=5:
                        self.Data['%s_kph'%var] = Quantidade( '%(name)s_%(unit)s' % {'name':name,'unit':units}, value, units)
                        units = ("%s"%quant[3].p.em.contents[0]).replace('<em>','').replace('</em>','')
                        value = "%s"%quant[4].p.contents[0]
                        self.Data['%s_dir'%var] = Quantidade( '%(name)s_%(unit)s' % {'name':name,'unit':units}, value, units)
                    else:
                        units = units.replace('<em>','').replace('</em>','')
                        self.Data[var] = Quantidade(name,value,units)
            self._IsDefined=True
    def _getDBconn(self):
        """Get a connection to an SQLite3 database and create the database table for this station is it does not exist. Remember to close() the connection afterwards!"""
        conn = sqlite3.connect(PYMETEOPT_DB)
        with conn:
            cur = conn.cursor()
            cur.execute('CREATE TABLE IF NOT EXISTS STATION08%03i(tstamp_utc TIMESTAMP PRIMARY KEY, Tar_C DOUBLE, pres_hPa DOUBLE, prec_mm DOUBLE, HRel DOUBLE, Wind_kph DOUBLE, Wind_dir TEXT)' % self.StationId)
        return conn
    def store(self):
        if self._IsStored:
            raise Exception('Already stored in DB!')
        if self._IsDefined:
            conn = self._getDBconn()
            with conn:
                cur = conn.cursor()
                try:
                    cur.execute('INSERT INTO STATION08%03i VALUES(?, ?,?,?,?, ?,?)'%self.StationId, (self.Timestamp, float(self.Data['t_ar']), float(self.Data['p']), float(self.Data['prec']), float(self.Data['rh']), float(self.Data['wind_kph']), str(self.Data['wind_dir']) ))
                except sqlite3.IntegrityError as error:
                    cur.execute('SELECT COUNT(*) FROM STATION08%03i WHERE tstamp_utc=?'%self.StationId, self.Timestamp)
                    numRows = cur.fetchone()[0]
                    if numRows > 0:
                        raise Exception('Observation %(tstamp)s for station %(station)s already in database; skipping.' % {'tstamp':self.Timestamp, 'station':self.StationId} )
                    else:
                        raise error
            conn.close()
            self._IsStored = True
        else:
            raise Exception('No data is loaded! Skipping.')
    def recall(self, timestamp):
        """Example: import datetime; e=Estacao(579); dt=datetime.datetime(2012,02,12,22); e.recall(dt)"""
        if self._hasStationId():
            if self._IsDefined:
                raise Exception('Already defined! Skipping.')
            if type(timestamp) != datetime.datetime:
                raise Exception('Argument must be of type datetime.')
            conn = self._getDBconn()
            with conn:
                cur = conn.cursor()
                cur.execute('SELECT COUNT(*) FROM STATION08%03i WHERE tstamp_utc=?'%self.StationId, (timestamp,))
                numRows = cur.fetchone()[0]
                if numRows == 1:
                    self._IsStored=True
                    cur.execute('SELECT * FROM STATION08%03i WHERE tstamp_utc=?'%self.StationId, (timestamp,))
                    row = cur.fetchone()
                    self.Timestamp = row[0]
                    self.Data['t_ar'] = Quantidade('Temp. ar', row[1], 'C')
                    self.Data['p'] = Quantidade('Pressao', row[2], 'hPa')
                    self.Data['prec'] = Quantidade('Precipitacao', row[3], 'mm')
                    self.Data['rh'] = Quantidade('Hum. rel.', row[4], '%')
                    self.Data['wind_kph'] = Quantidade('Vento vel.', row[5], 'km/h')
                    self.Data['wind_dir'] = Quantidade('Vento dir.', row[6], '')
                    self._IsDefined=True
                else:
                    print 'Observation for timestamp %(tstamp)s of station %(station)s is not on DB.' % {'tstamp':timestamp, 'station':self.StationId}
            conn.close()
            if self._IsDefined:
                return 1
            else:
                return 0
        else:
            raise Exception('No StationID!')

